﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using XMLGener.Interfaces;
using XMLGener.Utils;
using XMLGener.Data;
namespace XBasicModules.libs
{
    class PostgresSQLGetter : ModuleBase, XMLGenerModuleInterface
    {


        #region XMLGenerModuleInterface Members

        private int rowIterator = 0;
        /// <summary>
        /// Generate next number
        /// </summary>
        /// <returns>number</returns>
        public virtual string generate()
        {
            if (data == null)
                return "";
            String result = data.Rows[rowIterator++][0].ToString();
            if (rowIterator >= data.Rows.Count)
                rowIterator = 0;
            return result;

        }

        /// <summary>
        /// Retrurn definitnion of this module
        /// </summary>
        /// <returns>xml data</returns>
        public virtual string getDefinition()
        {
            return
              "<function>" +
              "    <name>PostgreSQL</name>" +
              "    <id>pgsql</id>" +
              "    <type>string</type>" +
              "    <attributes>" +
              "       <attribute>" +
              "          <name>Server</name>" +
              "          <id>server</id>" +
              "          <type>text</type>" +
              "       </attribute>" +
              "       <attribute>" +
              "          <name>Login</name>" +
              "          <id>login</id>" +
              "          <type>text</type>" +
              "       </attribute>" +
              "       <attribute>" +
              "          <name>Password</name>" +
              "          <id>password</id>" +
              "          <type>password</type>" +
              "       </attribute>" +
              "       <attribute>" +
              "          <name>Database</name>" +
              "          <id>database</id>" +
              "          <type>text</type>" +
              "       </attribute>" +
              "       <attribute>" +
              "          <name>Select(column) </name>" +
              "          <id>col</id>" +
              "          <type>text</type>" +
              "       </attribute>" +
              "       <attribute>" +
              "          <name>From(table) </name>" +
              "          <id>table</id>" +
              "          <type>text</type>" +
              "       </attribute>" +
              "       <attribute>" +
              "          <name>Where...</name>" +
              "          <id>where</id>" +
              "          <type>text</type>" +
              "       </attribute>" +
              "    </attributes>" +
              "</function>";
        }


        private String server = null;
        private String database = null;
        private String login = null;
        private String passwd = null;
        private String column = null;
        private String table = null;
        private String where = null;



        NpgsqlConnection psqlConn = null;

        /// <summary>
        /// Set attributes for this module before generating.
        /// </summary>
        /// <param name="xmlParams"></param>
        /// <param name="parentDocument"></param>
        public virtual void setParameters(string xmlParams, XMLGenerDocument parentDocument)
        {
            if (psqlConn != null)
                psqlConn.Close();
            if (!this.checkSettingsElement(xmlParams, parentDocument))
                return;
            this.server = this.getParam("server");
            this.login = this.getParam("login");
            this.passwd = this.getParam("password");
            this.database = this.getParam("database");
            this.column = this.getParam("col");
            this.table = this.getParam("table");
            this.where = this.getParam("where");



            string connStr = String.Format("Server={0};User ID={1};Password={2};Database={3};",
              server, login, passwd, database);


            psqlConn = new NpgsqlConnection(connStr);
            psqlConn.Open();

            GetPgSqlTables();
            psqlConn.Close();
            psqlConn = null;


        }
        DataTable data = null;

        private void GetPgSqlTables()
        {
            try
            {
                if (data == null)
                    data = new DataTable();

                String query = "SELECT " + column + " FROM " + table;
                if (where.Length > 0)
                    query = query + " WHERE " + where;

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, psqlConn);
                NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(da);

                da.Fill(data);
                if (data.Rows.Count < 1 || data.Columns.Count < 1)
                {
                    data = null;
                }
            }
            catch (Exception)
            {
                data = null;
                throw;
            }
        }


        #endregion

    }
}
